1. Field of the Invention
Embodiments of the present invention relate to information processing and more specifically to management of database triggers.
2. Background of the Related Art
Databases are computerized information storage and retrieval systems. A relational database management system (RDBMS) is a computer database management system that uses relational techniques for storing and retrieving data. Relational databases are computerized information storage and retrieval systems in which data in the form of tables (formally denominated xe2x80x9crelationsxe2x80x9d) are typically stored for use on disk drives or similar mass data stores. A xe2x80x9ctablexe2x80x9d includes a set of rows (formally denominated xe2x80x9ctuplesxe2x80x9d or xe2x80x9crecordsxe2x80x9d) spanning several columns. Reference is made to C. J. Date, An Introduction to Database Systems, 6th edition, Addison-Wesley Publishing Co. Reading, Mass. (1994) for an comprehensive general treatment of the relational database art.
An RDBMS is structured to accept commands to store, retrieve and delete data using high-level query languages such as the Structured Query Language (SQL). The term xe2x80x9cqueryxe2x80x9d denominates a set of commands for retrieving data from a stored database. The SQL standard has been promulgated by the International Standards Association since 1986.
A major goal of the underlying query compiler is to provide a suite of mechanisms so that user data can be easily stored and efficiently manipulated. In particular, an SQL query can declaratively specify the contents of a view. For relational databases, a view is essentially a virtual table having virtual rows and virtual columns of data. Although views are not directly mapped to real data in storage, views can be used for retrieval as if the data they represent is actually stored. A view can be used to present to a user a single logical view of information that is actually spread across one or more tables.
Regardless of the type and creator, tables are manipulated uniformly by means of the Data Manipulation Language (DML), such as those defined in the SQL standards. In some cases, a table is manipulated by the use of triggers. Triggers are procedures that are defined by users of the RDBMS. The RDBMS invokes triggers when particular events (associated with the triggers) occur. Specifically, a trigger program is written to take some action due to an insert, update, or delete operation against a table in a database. For example, in the case of an insert operation, a trigger can be defined such that it is invoked each time a row is inserted into a particular table. A trigger can instruct the system to take any number of actions when a specified change is attempted. Accordingly, triggers can be used to help maintain the integrity of the database.
Conventionally, triggers are defined through a particular interface such as high level query language (e.g., SQL). Database systems providing only one interface facilitate the management and consistency of triggers because trigger definition, database or table accesses, and execution is the same in each instance. However, a problem arises where multiple interfaces are supported by a database management system. For example, a system may provide high level query language interfaces and database management interfaces. Illustrative database management interfaces include GUI interfaces for PCs or workstations, application program interfaces (APIs), and command line interfaces for terminal type devices. One well known system that includes multiple interfaces to the DBMS is the eServer iSeries available from International Business Machines, Incorporated (IBM) of Armonk, N.Y.
In a database management system, that allows the definition of triggers, via either a high level query language or via database management interfaces, a method is required for determining which triggers should be activated upon a database operation occurring in the database. In some cases, specific control or management techniques are required by virtue of the trigger characteristics. For example, one well known trigger, referred to as a column trigger, is only activated upon a database operation to a specific column of data in a database table. Accordingly, the database management system needs a method for determining which columns of the database table the trigger is associated with, in order to determine which triggers should be activated upon a database operation occurring in the database.
The need for trigger management is compounded by the fact that one of the operations that a column trigger can perform is to modify the column values of the database table prior to the actual database operation (e.g., insert or update). Such triggers are known as xe2x80x9cbeforexe2x80x9d triggers, suggestive of their preemptive nature. Thus, the database management system also needs a method for determining which columns of the database table the trigger itself is modifying, so that in a network of triggers, the complete network of triggers is properly activated based on the originating database operation, and the operations of the triggers. In each case, the foregoing trigger management methods should be capable of supporting multiple interfaces.
Therefore, there is a need for a method and system for managing column triggers, particularly in a system having multiple interfaces.
Methods, systems, data structures and articles of manufacture are provided for tracking modifications to columns as a result of an I/O operation. In one embodiment a list is provided that allows for determining which triggers, both row and column oriented, should be activated upon the operation. In addition, a tracking mechanism is provided to track modifications to additional columns that occur because of the triggers being executed.
In another embodiment, a data structure is provided wherein the data structure comprises a trigger definition for a trigger. The trigger definition comprises specific column information indicating at least one column of a table for which the trigger is defined and modified column information indicating at least one column modified by execution of the trigger. The specific column information and the modified column information are configured to be compared to the contents of a tally to determine whether the trigger should be run.
In yet another embodiment, a method of managing triggers in a database containing a plurality of column triggers is provided. The method comprises processing an input/output (I/O) operation, wherein the I/O operation is configured to affect at least one column of a database object and priming a tally to reflect the at least one column. For each column trigger defined for the database object, the method further comprises determining whether the column trigger is configured to be executed in response to the I/O statement with reference to the tally.
Still another embodiment provides a method of managing triggers in a database containing a plurality of triggers. The method comprises processing an input/output (I/O) operation, wherein the I/O operation is configured to affect at least one column of a database object, priming a tally to reflect the at least one column and determining whether a trigger defined for the database object is a BEFORE trigger. If the trigger defined for the database object is a BEFORE trigger, then the method further comprises determining whether the trigger is a column trigger. If the trigger is a column trigger, then the method determines whether the trigger is configured to be executed in response to the I/O operation with reference to the tally, wherein the trigger is executed if an entry value of the tally matches an indicator value indicating that the trigger is defined for the at least one column.
Still another embodiment provides a signal bearing medium containing a program which, when executed by at least one processor, performs a method of managing triggers in a database containing a plurality of column triggers. The method comprises processing an input/output (I/O) operation, wherein the I/O operation is configured to affect at least one column of a database object and priming a tally to reflect the at least one column. For each column trigger defined for the database object, the method further comprises determining whether the column trigger is configured to be executed in response to the I/O statement with reference to the tally.